package com.ruoyi.system.dao;

import com.ruoyi.system.domain.ModuleEngInfo;
import com.ruoyi.system.domain.sqlite.*;
import com.ruoyi.system.domain.UploadInfo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import com.ruoyi.system.mapper.ModuleEngInfoMapper;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.File;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UploadDAO {

    private String sqliteEngPath;
    private String sqliteDBPath;

    private UploadInfo uploadInfo;

    private int volumeSerialInt;
    private Date dateTodayInDateType;
    private String dateToday;
    private SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");

//    private VolumeSerialNumber volumeSerialNumber;

    //防呆用工程id，根据工程表的id统一所有案卷id，不按规定操作导致的案卷信息工程id不一致
    private String newEngId = "";


    public UploadDAO(String sqliteEngPath, UploadInfo uploadInfo) {
        super();
        this.sqliteEngPath = sqliteEngPath;
        this.sqliteDBPath = sqliteEngPath + File.separator + "DangAn.db3";
        this.uploadInfo = uploadInfo;
        dateTodayInDateType = new Date();
        this.dateToday = sdf.format(dateTodayInDateType);
    }

    public UploadDAO(String sqliteEngPath) {
        super();
        this.sqliteEngPath = sqliteEngPath;
        this.sqliteDBPath = sqliteEngPath + File.separator + "DangAn.db3";
//        this.sqliteDBPath = sqliteEngPath + "\\DangAn.db3";
        dateTodayInDateType = new Date();
        this.dateToday = sdf.format(dateTodayInDateType);
    }

    private Connection connect() {
        String url = "jdbc:sqlite:" + sqliteDBPath;
        Connection conn = null;
        try {
            // 加载 SQLite 的 JDBC 驱动
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection(url);
        } catch (ClassNotFoundException e) {
            System.err.println("无法加载 SQLite JDBC 驱动");
            e.printStackTrace();
            // 或者抛出自定义异常，或者根据实际情况进行处理
        } catch (SQLException e) {
            System.err.println("无法建立与数据库的连接");
            e.printStackTrace();
            // 或者抛出自定义异常，或者根据实际情况进行处理
        }
        return conn;
    }

    /**
     * 从sqlite数据库获取工程
     * @return
     */
    public List<EngTable> getEng(){
        List<EngTable> engTables = new ArrayList<EngTable>();
        String sql = "SELECT * FROM Eng_Table";

        try (Connection conn = this.connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)){

            while (rs.next()){
                EngTable engTable = new EngTable();
                int i = 1;
                //将所有字段存入
                engTable.setId 				  (rs.getString(i));i++;
                engTable.setEngName           (rs.getString(i));i++;
                engTable.setEngNo             (rs.getString(i));i++;
                engTable.setDangAnGuanNo      (rs.getString(i));i++;
                engTable.setYiJiaoDanWei      (rs.getString(i));i++;
                engTable.setBianZhiDateStart  (transferDateString(rs.getString(i)));  i++;
                engTable.setBianZhiDateEnd    (transferDateString(rs.getString(i)));  i++;
                engTable.setEngAddress		  (rs.getString(i));i++;
                engTable.setAnJuanShu         (rs.getString(i));i++;
                engTable.setDangAnGuanName    (rs.getString(i));i++;
                engTable.setShuZiHuaDanWei    (rs.getString(i));i++;
                engTable.setBuildingOrg       (rs.getString(i));i++;
                engTable.setOperationOrg      (rs.getString(i));i++;
                engTable.setCheckOrg          (rs.getString(i));i++;
                engTable.setDesignOrg         (rs.getString(i));i++;
                engTable.setExplorationOrg    (rs.getString(i));i++;
                engTable.setSupervisionOrg    (rs.getString(i));i++;
                engTable.setCheckIssueNo      (rs.getString(i));i++;
                engTable.setPlanLicenseNo     (rs.getString(i));i++;
                engTable.setLandPlanLicenseNo (rs.getString(i));i++;
                engTable.setLandLicenseNo     (rs.getString(i));i++;
                engTable.setOperationLicenseNo(rs.getString(i));i++;
                engTable.setStartStation      (rs.getString(i));i++;
                engTable.setEndStation        (rs.getString(i));i++;
                engTable.setTotalLength       (rs.getString(i));i++;
                engTable.setProjManager       (rs.getString(i));i++;
//                engTable.setQuXian            (rs.getString(i));i++;
                engTables.add(engTable);
            }
        }catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return engTables;
    }

    /**
     * 从sqlite数据库获取案卷信息
     * @return
     */
    public List<VolumeInfo> getVolume() {
        List<VolumeInfo> volumeInfos = new ArrayList<VolumeInfo>();
        String sql = "SELECT * FROM volume_info";

        try (Connection conn = this.connect();
             Statement stmt  = conn.createStatement();
             ResultSet rs    = stmt.executeQuery(sql)){

            // loop through the result set
            while (rs.next()) {
                VolumeInfo volumeInfo = new VolumeInfo();
                // The column count starts from 1
                int i = 1;
                //将所有字段存入
                volumeInfo.setId                (rs.getString(i));i++;
                volumeInfo.setEngId             (rs.getString(i));i++;
                volumeInfo.setVolumeName        (rs.getString(i));i++;
                volumeInfo.setVolumeNo          (rs.getString(i));i++;
                volumeInfo.setCarrierType       (rs.getString(i));i++;
                volumeInfo.setAmount            (rs.getString(i));i++;
                volumeInfo.setSize              (rs.getString(i));i++;
                volumeInfo.setBuildOrganization (rs.getString(i));i++;
                volumeInfo.setStoreType         (rs.getString(i));i++;
                volumeInfo.setSecurityLayer     (rs.getString(i));i++;
                volumeInfo.setFileStart         (transferDateString(rs.getString(i)));  i++;
                volumeInfo.setFileEnd           (transferDateString(rs.getString(i)));  i++;
                volumeInfo.setInjectionPerson   (rs.getString(i));i++;
                volumeInfo.setCheckPerson       (rs.getString(i));i++;
                volumeInfo.setInjection         (transferDateString(rs.getString(i)));  i++;
                volumeInfo.setCheckDate         (transferDateString(rs.getString(i)));  i++;
                volumeInfo.setStorePosition     (rs.getString(i));i++;
                volumeInfo.setComments          (rs.getString(i));i++;
//                volumeInfo.setLuiShui           (rs.getString(i));i++;
                volumeInfos.add(volumeInfo);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return volumeInfos;
    }

    /**
     * 从sqlite数据库获取文件信息
     * @return
     */
    public List<FileInfo> getFile() {
        List<FileInfo> fileInfos = new ArrayList<FileInfo>();
        String sql = "SELECT * FROM file_info";

        try (Connection conn = this.connect();
             Statement stmt  = conn.createStatement();
             ResultSet rs    = stmt.executeQuery(sql)){

            // loop through the result set
            while (rs.next()) {
                FileInfo fileInfo = new FileInfo();
                // The column count starts from 1
                int i = 1;
                //将所有字段存入
                fileInfo.setId 					(rs.getString(i));i++;
                fileInfo.setVolumeId            (rs.getString(i));i++;
                fileInfo.setFileName            (rs.getString(i));i++;
                fileInfo.setDutyOrganization    (rs.getString(i));i++;
                fileInfo.setStoreType           (rs.getString(i));i++;
                fileInfo.setFileTime            (transferDateString(rs.getString(i)));  i++;
                fileInfo.setFileNo              (rs.getString(i));i++;
                fileInfo.setPageNumber          (rs.getString(i));i++;
                fileInfo.setBuildOrganization   (rs.getString(i));i++;
                fileInfo.setCarrierType         (rs.getString(i));i++;
                fileInfo.setPaperType           (rs.getString(i));i++;
                fileInfo.setSecurityLayer       (rs.getString(i));i++;
                fileInfo.setComments            (rs.getString(i));i++;
                fileInfo.setAbstract            (rs.getString(i));i++;
                fileInfo.setKeyWords            (rs.getString(i));i++;
                fileInfos.add(fileInfo);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return fileInfos;
    }

    private Date transferDateString(String dateString) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date d = sdf.parse(dateString);
            if (d.compareTo(new Date(1)) > 0) {
                return d;
            } else {
                return null;
            }
        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }
    }

}
